﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SaludOcupacional.Exceptions;
using SaludOcupacional.Model;
using System.Collections.ObjectModel;

namespace SaludOcupacional.DataAccess
{
    public class CarnetDataAccess : IDisposable
    {
        private DatabaseManager dbManager;

        public void Dispose()
        {
            dbManager.Dispose();
        }
        public CarnetDataAccess()
        {
            dbManager = new DatabaseManager();
        }

        public Carnet BuscarAnalisisPorCi(int ci)
        {
            if (ci == 0)
            {
                throw new ArgumentNullException("ci");
            }
            // Create a Sql sentence
            string sqlsentence = "SELECT * FROM [CarnetSanitario] WHERE ci_empleado = @Ci";

            dbManager.Command.CommandText = sqlsentence;
            dbManager.Command.Parameters.AddWithValue("@Ci", ci);

            Carnet resp = null;

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow reader = dataSet.Tables[0].Rows[0];

                    resp = new Carnet
                    {
                        CodCarnet = Convert.ToInt32(reader["cod_carnet"], System.Globalization.CultureInfo.InvariantCulture),
                        CiEmpleado = Convert.ToInt32(reader["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),
                        Vdrl = reader["vdrl"].ToString(),
                        Ascaris = reader["ascaris"].ToString(),
                        Amebas = reader["amebas"].ToString(),
                        Giardias = reader["giardias"].ToString(),
                        Nematodos = reader["nematodos"].ToString(),
                        Trematodos = reader["trematodos"].ToString(),
                        Trichuris = reader["trichuris"].ToString(),
                        OtrosParasitos = reader["otros_parasitos"].ToString(),
                        SinParasitos = reader["sin_parasitos"].ToString(),
                        Acciones = reader["acciones_atomar"].ToString(),
                        Observaciones = reader["observaciones"].ToString(),
                        Fecha = reader["fecha"].ToString()
                    };
                }
            }
            catch (Exception)
            {
                throw;
            }

            return resp;
        }

        public void RegistrarAnalisis(Carnet carnet)
        {
            if (carnet == null)
            {
                throw new LoginException("Invalid Input Parameters");
            }

            string sqlSentence = "INSERT INTO [dbo].[CarnetSanitario]([ci_empleado],[vdrl],[ascaris],[amebas],[giardias],[nematodos],[trematodos],[trichuris],[otros_parasitos],[sin_parasitos],[acciones_atomar],[observaciones],[fecha])VALUES ( ";
            sqlSentence += "@Ci, @Vdrl, @Ascaris, @Amebas, @Giardias, @Nematodos, @Trematodos, @Trichuris, @Otros, @Sin, @Acciones, @Observaciones, @Fecha )";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;



            dbManager.Command.Parameters.AddWithValue("@Ci", carnet.CiEmpleado);
            dbManager.Command.Parameters.AddWithValue("@Vdrl", carnet.Vdrl);
            dbManager.Command.Parameters.AddWithValue("@Ascaris", carnet.Ascaris);
            dbManager.Command.Parameters.AddWithValue("@Amebas", carnet.Amebas);
            dbManager.Command.Parameters.AddWithValue("@Giardias", carnet.Giardias);
            dbManager.Command.Parameters.AddWithValue("@Nematodos", carnet.Nematodos);
            dbManager.Command.Parameters.AddWithValue("@Trematodos", carnet.Trematodos);
            dbManager.Command.Parameters.AddWithValue("@Trichuris", carnet.Trichuris);
            dbManager.Command.Parameters.AddWithValue("@Otros", carnet.OtrosParasitos);
            dbManager.Command.Parameters.AddWithValue("@Sin", carnet.SinParasitos);
            dbManager.Command.Parameters.AddWithValue("@Acciones", carnet.Acciones);
            dbManager.Command.Parameters.AddWithValue("@Observaciones", carnet.Observaciones);
            dbManager.Command.Parameters.AddWithValue("@Fecha", carnet.Fecha);
           


            try
            {
                int value = dbManager.NonQuery();

                if (value < 1)
                {
                    throw new LoginException("The record wasn't registered");
                }
            }
            catch (Exception e)
            {
                throw new LoginException("Error creating a user" + e.Message, e);
            }
        }

        public void EditarAnalisis(Carnet carnet)
        {
            if (carnet == null)
            {
                throw new LoginException("The Analisis can´t be null");
            }

            string sqlSentence =
                "UPDATE [CarnetSanitario]  SET [vdrl] = @Vdrl, [ascaris] = @Ascaris, [amebas] = @Amebas, [giardias] = @Giardias, ";
            sqlSentence = sqlSentence + "[nematodos] = @Nematodos, [trematodos] = @Trematodos, ";
            sqlSentence = sqlSentence + "[trichuris] = @Trichuris, [otros_parasitos] = @Otros, ";
            sqlSentence = sqlSentence + "[sin_parasitos] = @Sin, [acciones_atomar] = @Acciones, ";
            sqlSentence = sqlSentence + "[observaciones] = @Observaciones, [fecha] = @Fecha ";

            sqlSentence = sqlSentence + " WHERE [ci_empleado] = @Ci";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;




            
            dbManager.Command.Parameters.AddWithValue("@Vdrl", carnet.Vdrl);
            dbManager.Command.Parameters.AddWithValue("@Ascaris", carnet.Ascaris);
            dbManager.Command.Parameters.AddWithValue("@Amebas", carnet.Amebas);
            dbManager.Command.Parameters.AddWithValue("@Giardias", carnet.Giardias);
            dbManager.Command.Parameters.AddWithValue("@Nematodos", carnet.Nematodos);
            dbManager.Command.Parameters.AddWithValue("@Trematodos", carnet.Trematodos);
            dbManager.Command.Parameters.AddWithValue("@Trichuris", carnet.Trichuris);
            dbManager.Command.Parameters.AddWithValue("@Otros", carnet.OtrosParasitos);
            dbManager.Command.Parameters.AddWithValue("@Sin", carnet.SinParasitos);
            dbManager.Command.Parameters.AddWithValue("@Acciones", carnet.Acciones);
            dbManager.Command.Parameters.AddWithValue("@Observaciones", carnet.Observaciones);
            dbManager.Command.Parameters.AddWithValue("@Fecha", carnet.Fecha);
            dbManager.Command.Parameters.AddWithValue("@Ci", carnet.CiEmpleado);


            try
            {
                int value = dbManager.NonQuery();
                if (value < 1)
                {
                    throw new LoginException("The change wasn't registered");
                }

            }
            catch (Exception e)
            {
                throw new LoginException("Error edit profile", e);
            }
        }
    }
}
